import pandas as pd
from sklearn.preprocessing import MinMaxScaler
import pymysql
import pymysql.cursors

class MysqlUtils(object):
    def __init__(self, *args):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='root',
            db='scenic',
            port=3306,
            charset='utf8'
        )
    def is_holiday(self, date):
        """是否节假日判断
        """
        
    def get_scenic_data(self):
        """获取数据
        """
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT DATE(g.create_time) as date, HOUR(g.create_time) as count FROM order_user_gate_rel g w
        """
        cursor.execute(sql)
        ret = cursor.fetchall()

        df = pd.DataFrame(ret)
        #print(df.head)
        #格式转换
        date_range = pd.date_range(start='2024-07-01', end='2025-03-02', freq='D')
        hours = range(6, 24)
        full_index =pd.MultiIndex.from_product([date_range, hours], names=['date', 'hour'])
        #print(full_index)
        df_full = df.set_index(['date', 'hour']).reindex(full_index, fill_value=0).reset_index()
        # 按天组织数据，每行包含18小时的检票次数        
        df_pivot = df_full.pivot(index='date', columns='hour', values='count')
        # print(df_pivot)
        df_pivot['dow'] = df_pivot.index.dayofweek
        df_pivot['month'] = df_pivot.index.month
        #print(df_pivot)
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
        # 对星期几
        df_pivot = pd.get_dummies(df_pivot, columns=['doe', 'month'], dtype=int)
        #归一化
        hours_columns = list(range(6, 24))
        df_hours = df_pivot[hours_columns].copy()
        feature_colunms = [col for col in df_pivot.columns if col not in hours_columns]
        df_feature = df_pivot[feature_colunms].copy()

        scaler = MinMaxScaler()
        scaled_hours = scaler.fit_transform(df_hours)
        #将
        df_hours_scaled  = pd.DataFrame(scaled_hours, columns=hours_columns, index=df_hours.index)
        #合并
        df_pivot_clean = pd.concat([df_hours_scaled, df_feature], axis=1)
        print(df_pivot_clean)
        df_pivot_clean.to_csv('NN/scenic_data.csv', index=False)

if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()

